﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Configuration;
using System.Data.OleDb;
using System.Data;

namespace Windvoice.Util
{
    public class AccessHelper
    {
        private static OleDbConnection AccessConnection = new OleDbConnection();
        private static OleDbCommand AccessCommand = new OleDbCommand();

        public static DataSet ExecuteDataSet(string ConnectionString,string CommandText)
        {
            try
            {
                AccessConnection.ConnectionString = ConnectionString;
                AccessConnection.Open();
                AccessCommand.CommandText = CommandText;
                AccessCommand.Connection = AccessConnection;

                OleDbDataAdapter da = new OleDbDataAdapter(AccessCommand);
                DataSet ds = new DataSet();

                da.Fill(ds);
                return ds;
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                AccessCommand.Dispose();
                AccessConnection.Close();
            }
        }

        public static object ExecuteScaler(string ConnectionString, string CommandText)
        {
            try
            {
                AccessConnection.ConnectionString = ConnectionString;
                AccessConnection.Open();
                AccessCommand.CommandText = CommandText;
                AccessCommand.Connection = AccessConnection;

                return AccessCommand.ExecuteScalar();
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                AccessCommand.Dispose();
                AccessConnection.Close();
            }
        }

        public static int ExecuteNonQuery(string ConnectionString, string CommandText)
        {
            try
            {
                AccessConnection.ConnectionString = ConnectionString;
                AccessConnection.Open();
                AccessCommand.CommandText = CommandText;
                AccessCommand.Connection = AccessConnection;

                return AccessCommand.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                AccessCommand.Dispose();
                AccessConnection.Close();
            }
        }

        public static OleDbDataReader ExecuteReader(string ConnectionString, string CommandText)
        {
            try
            {
                AccessConnection.ConnectionString = ConnectionString;
                AccessConnection.Open();
                AccessCommand.CommandText = CommandText;
                AccessCommand.Connection = AccessConnection;

                return AccessCommand.ExecuteReader();
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                AccessCommand.Dispose();
                AccessConnection.Close();
            }
        }

        /// <summary> 
        /// 获取分页数据集 
        /// </summary> 
        /// <param name="pageIndex">要显示的页的索引,第一页是1</param> 
        /// <param name="pageSize">每页要显示的记录的数目</param> 
        /// <param name="recordCount">返回符合条件的总记录数</param> 
        /// <param name="tableName">要查询的数据表</param> 
        /// <param name="queryFields">要查询的字段</param> 
        /// <param name="primaryKey">主键字段</param> 
        /// <param name="ascending">是否为升序排列</param> 
        /// <param name="condition">查询的筛选条件,注：不要加诸如1=1这样的条件</param> 
        /// <returns>返回分页dataset。</returns> 
        public static DataSet GetDataPage(string ConnectionString,int pageIndex, int pageSize, out int recordCount, string tableName, string queryFields, string primaryKey, bool ascending, string condition)
        {
            string strSQL;
            string strWhere = (condition == string.Empty) ? "" : " where " + condition;            //组合where语句 
            string strOrderBy = " order by " + primaryKey + (ascending ? " ASC" : " DESC"); //组合order by语句 
            ADOR.Recordset rs = new ADOR.Recordset();

            //获取记录总数,注：用rs.RecordCount方法取会慢 
            strSQL = "select count(" + primaryKey + ") from " + tableName + strWhere;
            rs.Open(strSQL, ConnectionString, ADOR.CursorTypeEnum.adOpenKeyset, ADOR.LockTypeEnum.adLockReadOnly, 1);
            recordCount = (int)rs.Fields[0].Value;

            //算出总页数 
            int allPages = (int)((double)Math.Ceiling((double)recordCount / (double)pageSize));

            //如果无记录或请求的页超出范围则返回空记录 
            if ((recordCount == 0) || (pageIndex > allPages) || (pageIndex <= 0))
                return AccessHelper.ExecuteDataSet(ConnectionString, "select " + queryFields + " from " + tableName + " where 1=2");

            //用Recordset加载全部记录 
            strSQL = "select " + queryFields + " from " + tableName + strWhere + strOrderBy;
            if (rs.State == 1) rs.Close();
            rs.Open(strSQL, ConnectionString, ADOR.CursorTypeEnum.adOpenKeyset, ADOR.LockTypeEnum.adLockReadOnly, 1);

            //定位记录指针到所在页 
            rs.AbsolutePosition = (ADOR.PositionEnum_Param)((pageIndex - 1) * pageSize + 1);

            //获取字段数组 
            int fieldsCount = rs.Fields.Count;
            object[] oFields = new object[fieldsCount];
            for (int i = 0; i < fieldsCount; i++)
                oFields[i] = rs.Fields[i].Name.ToString();

            //从记录指针位置取出需要记录，返回的是一个二维数组 
            object[,] arr = (object[,])rs.GetRows(pageSize, "", oFields);

            //===================将获取的二维数组转成dataset======================= 

            //获取空表结构 
            DataSet ds = new DataSet();
            strSQL = "select " + queryFields + " from " + tableName + " where 1=2";
            ds = AccessHelper.ExecuteDataSet(ConnectionString,strSQL);
            //记录行数 
            int recRows = arr.GetUpperBound(1);
            //填充dataset 
            for (int i = 0; i <= recRows; i++)         //行 
            {
                DataRow dr = ds.Tables[0].NewRow();
                for (int j = 0; j < fieldsCount; j++)   //列 
                {
                    dr[j] = arr[j, i];
                }
                ds.Tables[0].Rows.Add(dr);
            }
            return ds;
        } 

    }
}
